package com.turing.post.cardmanagement.impl;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.cardmanagement.util.ActivateCard;
import com.turing.post.cardmanagement.util.ActivateCardDao;
import com.turing.post.cardmanagement.util.CardStock;
import com.turing.post.cardmanagement.util.MakeCardControl;
import com.turing.post.cardmanagement.util.MakeCardControlDao;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;
import com.turing.post.util.SHA1;

@Stateless(mappedName = "cardmanagement.impl.ActivateCardDaoImpl")
@Remote
@Local
public class ActivateCardDaoImpl implements ActivateCardDao {

	@EJB(mappedName = "cardmanagement.impl.MakeCardControlDaoImpl")
	private MakeCardControlDao makeCardControlDaoImpl;
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	@Override
	public ActivateCard getActivateCardByCardNo(String cardNo) {
		return _em.find(ActivateCard.class, cardNo);
	}

	@SuppressWarnings("unchecked")
	@Override
	public void updateActivateCardPwd(Integer key, Integer count,
			String issueCompanyNo) throws PCPSApplicationException {
		String sql = "select * from ActivateCard where batchNo = '"
				+ issueCompanyNo + "' order by cardSeqNo";
		Query query = _em.createNativeQuery(sql, ActivateCard.class);
		int maxResult = count;
		int startPosition = maxResult * key;
		query.setFirstResult(startPosition);
		query.setMaxResults(maxResult);
		List<ActivateCard> aclist = query.getResultList();
		String pwd = "00000000";
		if (aclist != null && aclist.size() > 1) {
			// System.out.println("密码修改开始" + aclist.size());
			// int a = 0;
			for (ActivateCard ac : aclist) {
				// System.out.println(a++);
				ac.setCardPwd(new SHA1()
						.getDigestOfString((ac.getCardNo() + pwd).getBytes()));
				update(ac);
			}
			// System.out.println("密码修改完成" + aclist.size());
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	@TransactionAttribute(javax.ejb.TransactionAttributeType.REQUIRES_NEW)
	public void updateActivateCardPwdByCardNo(String cardNo)
			throws PCPSApplicationException {
		String pwd = "00000000";
		ActivateCard ac = getActivateCardByCardNo(cardNo);
		ac.setCardPwd(new SHA1().getDigestOfString((ac.getCardNo() + pwd)
				.getBytes()));
		update(ac);
	}

	@SuppressWarnings("unchecked")
	@Override
	public ActivateCard getActivateCardByCardNoOrSeqNo(String cardNo) {
		Query query = _em
				.createNativeQuery(
						"select t.* from ActivateCard t where t.CardNo = ?1 or t.CardSeqNo= ?1",
						ActivateCard.class);
		query.setParameter(1, cardNo);
		List<ActivateCard> aclist = query.getResultList();
		if (aclist == null || aclist.size() != 1)
			return null;
		ActivateCard ac = aclist.get(0);
		_em.refresh(ac);
		return ac;
	}

	@Override
	@SuppressWarnings("unchecked")
	public ActivateCard getActivateCardByCardNoForUpdate(String cardNo) {
		Query query = _em
				.createNativeQuery(
						"select t.* from ActivateCard t where t.cardNo = ?1 for update",
						ActivateCard.class);
		query.setParameter(1, cardNo);
		List<ActivateCard> aclist = query.getResultList();
		if (aclist == null || aclist.size() != 1)
			return null;
		ActivateCard ac = aclist.get(0);
		_em.refresh(ac);
		return getActivateCardByCardNo(ac.getCardNo());
	}

	/**
	 * 根据卡序号获取激活卡对象
	 * 
	 * @param cardSeqNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public ActivateCard getActivateCardByCardSeqNo(String cardSeqNo) {
		Query query = _em.createNativeQuery(
				"select t.* from ActivateCard t where t.CardSeqNo = ?1",
				ActivateCard.class);
		query.setParameter(1, cardSeqNo);
		List<ActivateCard> aclist = query.getResultList();
		if (aclist == null || aclist.size() != 1)
			return null;
		ActivateCard ac = aclist.get(0);
		_em.refresh(ac);
		return ac;
	}

	/**
	 * 根据卡2、3磁道信息获取激活卡对象
	 * 
	 * @param cardNo
	 *            卡号
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public ActivateCard getActivateCardByTrack23(String track2, String track3) {
		Query query = _em
				.createNativeQuery(
						"select t.* from ActivateCard t where t.TWOTRACKCONTENT = ?1 and ( ?2 = '' OR t.THIRDTRACKCONTENT = ?2 )",
						ActivateCard.class);
		if (PCPSUtil.isEmpty(track2))
			return null;
		if (PCPSUtil.isEmpty(track3))
			track3 = "";
		query.setParameter(1, track2);
		query.setParameter(2, track3);
		List<ActivateCard> aclist = query.getResultList();
		if (aclist == null || aclist.size() != 1)
			return null;
		ActivateCard ac = aclist.get(0);
		_em.refresh(ac);
		return ac;
	}

	@Override
	public void insert(ActivateCard ac) throws PCPSApplicationException {
		GeneralDao.insert(_em, ac);
	}

	@Override
	public void update(ActivateCard ac) throws PCPSApplicationException {
		GeneralDao.update(_em, ac);
	}

	/**
	 * 置空主帐号
	 * 
	 * @param cardNo
	 */
	public void clearMasterAccNo(String cardNo) {
		Query query = _em
				.createNativeQuery("update ActivateCard t set t.masterAccNo = null where t.cardNo = ?1");
		query.setParameter(1, cardNo);
		query.executeUpdate();
	}

	/**
	 * 删除
	 * 
	 * @param ac
	 * @throws PCPSApplicationException
	 */
	public void remove(ActivateCard ac) throws PCPSApplicationException {
		// 必须这样才能删掉!!!!!!
		GeneralDao.remove(_em, getActivateCardByCardNo(ac.getCardNo()));
	}

	/**
	 * 因密码错,更新密码错误计数
	 */
	@TransactionAttribute(javax.ejb.TransactionAttributeType.REQUIRES_NEW)
	public void incPwdErrCnt(String CardNo) {
		Query qry = _em
				.createNativeQuery("UPDATE ActivateCard SET PwdErrCnt = PwdErrCnt + 1, LastPwdErrTime = SysDate WHERE CardNo = ?1");
		qry.setParameter(1, CardNo);
		qry.executeUpdate();
		getActivateCardByCardNoForUpdate(CardNo);
	}

	/**
	 * 复位密码错误计数
	 */
	@TransactionAttribute(javax.ejb.TransactionAttributeType.REQUIRES_NEW)
	public void resetPwdErrCnt(String CardNo) {
		Query qry = _em
				.createNativeQuery("UPDATE ActivateCard SET PwdErrCnt = 0 WHERE CardNo = ?1");
		qry.setParameter(1, CardNo);
		qry.executeUpdate();
		getActivateCardByCardNoForUpdate(CardNo);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ActivateCard> getActivateCardListByCardSeqNo(
			String startCardSeqNo, String endCardSeqNo, String batchNo) {
		String sql = "select * from ActivateCard where BatchNo='" + batchNo
				+ "' and cardseqno '" + startCardSeqNo + "' and '"
				+ endCardSeqNo + "' order by CardSeqNo";
		Query query = _em.createNativeQuery(sql, ActivateCard.class);
		return query.getResultList();
	}

	@Override
	public ActivateCard checkCardExistInActivateCard(String batchNo,
			String cardSeqNo) {
		String sql = "select * from ActivateCard where BatchNo='" + batchNo
				+ "' and CardSeqNo='" + cardSeqNo + "')";
		Query query = _em.createNativeQuery(sql, ActivateCard.class);
		return (ActivateCard) query.getSingleResult();
	}

	/**
	 * 根据销售单ID检索激活卡列表
	 * 
	 * @param saleOrderId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<ActivateCard> getAllActivateCardListBySaleOrderId(
			BigInteger saleOrderId) {
		Query qry = _em
				.createNativeQuery(
						"select t.* from ActivateCard t WHERE t.saleOrderId = ?1 for update",
						ActivateCard.class);
		qry.setParameter(1, saleOrderId);
		return qry.getResultList();
	}

	@Override
	public boolean checkOperIsInIssueCompany(String issueCompanyNo,
			String cardNo) {
		if (PCPSUtil.isEmpty(issueCompanyNo) || PCPSUtil.isEmpty(cardNo)) {
			return false;
		}
		issueCompanyNo = PCPSUtil.getICNoByBranchNo(issueCompanyNo);
		ActivateCard ac = getActivateCardByCardNo(cardNo);
		if (ac == null) {
			ac = getActivateCardByCardSeqNo(cardNo);
		}
		if (ac == null) {
			return false;
		}
		if (ac != null) {
			MakeCardControl mcc = makeCardControlDaoImpl
					.getMakeCardControlByBatchNo(ac.getBatchNo());
			if (mcc != null) {
				if (mcc.getIssueCompanyNo().equals(issueCompanyNo)) {
					return true;
				} else {
					return false;
				}
			} else {
				return false;
			}
		}
		return false;
	}

	@Override
	public void setTableFieldIsNull(String sql) throws PCPSApplicationException {
		try {
			Query query = _em.createNativeQuery(sql);
			query.executeUpdate();
		} catch (Exception ex) {
			PCPSUtil.Log("更新异常", ex);
			throw new PCPSApplicationException(
					PCPSApplicationException.ERRCODE_CATEGORY_DATABASE, "",
					"数据更新异常!", false);
		}
	}

	@Override
	public boolean checkCardStatusIsNormal(ActivateCard ac) {
		// ActivateCard ac = getActivateCardByCardNo(cardNo);
		if (ac == null)
			return false;
		// CloseStatus 销卡状态 char(1) Not Null 0-未销卡
		// LossStatus 挂失状态 char(1) Not Null 0-正常
		// ManualLockStatus 手工锁定状态 char(1) Not Null 0-未锁定
		if (ac.getCloseStatus().equals("0") && ac.getLossStatus().equals("0")
				&& ac.getManualLockStatus().equals("0")) {
			return true;
		}
		return false;
	}

	// 过期卡明细表
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getExpiredCardDetailByCondition(
			PCPSWebInput webInput, String serchBranchNo) {
		String sql = "select b.salebranchname,c.cardclassicname,t.cardno,t.balance/100,to_char(t.opentime,'yyyy-mm-dd hh24:mi:ss') opentime,to_char(t.expired,'yyyy-mm-dd') expired from activatecard t,cardclassic c,salebranch b where  t.cardclassicid=c.cardclassicid and t.salebranchno=b.salebranchno and t.expired is not null and t.expired<sysdate";
		sql += PCPSUtil.getSqlCond(webInput, serchBranchNo, "t.salebranchno");
		sql += " order by t.salebranchno";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}

	// 过期卡统计表
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getExpiredCardCountByCondition(PCPSWebInput webInput,
			String serchBranchNo) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String sql = "select b.salebranchname,c.cardclassicname,a.count,a.balance from(select t.salebranchno,t.cardclassicid,count(*) count,nvl(sum(t.balance),0)/100 balance from activatecard t where t.expired is not null and t.expired<sysdate";
		sql += PCPSUtil.getSqlCond(webInput, serchBranchNo, "t.salebranchno");
		sql += " group by t.salebranchno,t.cardclassicid order by salebranchno,cardclassicid)a,salebranch b,cardclassic c where a.salebranchno=b.salebranchno and a.cardclassicid=c.cardclassicid order by a.salebranchno,c.cardclassicid";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();

	}

	@SuppressWarnings("unchecked")
	@Override
	public String[] getCardNoByTrack23(PCPSWebInput webInput, String mode,
			String track2, String track3) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| (PCPSUtil.isEmpty(track2) && PCPSUtil.isEmpty(track3))) {
			return null;
		}
		String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		if (PCPSUtil.isEmpty(mode)) {
			String sql = "select * from cardstock where batchno like '"
					+ issueCompanyNo
					+ "%' and (?1 is null or TwoTrackContent=?1) and (?2 is null or ThirdTrackContent=?2)";
			Query query = _em.createNativeQuery(sql, CardStock.class);
			query.setParameter(1, track2);
			query.setParameter(2, track3);
			List<CardStock> csList = query.getResultList();
			if (csList != null && csList.size() > 0) {
				return new String[] { csList.get(0).getCardSeqNo(),
						csList.get(0).getCardNo() };
			} else {
				sql = "select * from ActivateCard where batchno like '"
						+ issueCompanyNo
						+ "%' and (?1 is null or TwoTrackContent=?1) and (?2 is null or ThirdTrackContent=?2)";
				Query query1 = _em.createNativeQuery(sql, ActivateCard.class);
				query1.setParameter(1, track2);
				query1.setParameter(2, track3);
				List<ActivateCard> acList = query1.getResultList();
				if (acList != null && acList.size() > 0) {
					return new String[] { acList.get(0).getCardSeqNo(),
							acList.get(0).getCardNo() };
				} else {
					return null;
				}
			}
		}
		if ("1".equals(mode)) {
			String sql = "select * from cardstock where batchno like '"
					+ issueCompanyNo
					+ "%' and (?1 is null or TwoTrackContent=?1) and (?2 is null or ThirdTrackContent=?2)";
			Query query = _em.createNativeQuery(sql, CardStock.class);
			query.setParameter(1, track2);
			query.setParameter(2, track3);
			List<CardStock> csList = query.getResultList();
			if (csList != null && csList.size() > 0) {
				return new String[] { csList.get(0).getCardSeqNo(),
						csList.get(0).getCardNo() };
			} else {
				return null;
			}
		}
		if ("2".equals(mode)) {
			String sql = "select * from ActivateCard where batchno like '"
					+ issueCompanyNo
					+ "%' and (?1 is null or TwoTrackContent=?1) and (?2 is null or ThirdTrackContent=?2)";
			Query query1 = _em.createNativeQuery(sql, ActivateCard.class);
			query1.setParameter(1, track2);
			query1.setParameter(2, track3);
			List<ActivateCard> acList = query1.getResultList();
			if (acList != null && acList.size() > 0) {
				return new String[] { acList.get(0).getCardSeqNo(),
						acList.get(0).getCardNo() };
			} else {
				return null;
			}
		}
		return null;
	}

	/**
	 * 持卡人账户报表
	 * 
	 * @param webInput
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getActivatecardByOwnerAll(PCPSWebInput webInput) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select a.cardno,b.cardclassicname,a.balance from ActivateCard  a,CardClassic b"
				+ " where a.cardclassicid=b.cardclassicid   and a.BatchNo like '"
				+ issuecompanyno
				+ "%' and  a.Balance>0 and  a.CloseStatus=0  order by a.CardNo desc";

		Query query = _em.createNativeQuery(sql);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getActivateCardsOwnerInfo(String issueCompanyNo) {
		String sql = "select a.cardno,b.ownerid from activatecard a,("
				+ " select t.ownerid,count(*) from activatecard t"
				+ " where t.ownerid is not null group by t.ownerid"
				+ " having count(*)>1)b where a.ownerid=b.ownerid"
				+ " and a.batchno like '" + issueCompanyNo + "%'";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getActivateCardsMasterAccOwnerInfo(
			String issueCompanyNo) {
		String sql = "select a.ownerid,case when a.masteraccno is null then (select t.masteraccno from activatecard t where t.cardno=a.newcardno) else a.masteraccno end as masteraccno,a.cardno from activatecard a,("
				+ " select t.ownerid from activatecard t where t.ownerid is not null"
				+ " group by t.ownerid ) b"
				+ " where a.ownerid=b.ownerid"
				+ " and a.batchno like '"
				+ issueCompanyNo
				+ "%'"
				+ " order by a.ownerid,a.masteraccno,a.cardno";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getActivateCardsResidualDetail(String isSingle,
			String saleBranchNo, BigInteger balanceUpperLimit) {
		String sql = "SELECT X.CARDNO, X.SALEBRANCHNO, X.SALEBRANCHNAME, X.CARDFACEID, X.CARDFACENAME, X.BALANCE, TO_CHAR(MAX(Y.TRANTIME), 'YYYY-MM-DD HH24:MI:SS') "
				+ "FROM "
				+ "(SELECT A.CARDNO CARDNO, A.SALEBRANCHNO SALEBRANCHNO, "
				+ "(SELECT B.SALEBRANCHNAME FROM SALEBRANCH B WHERE B.SALEBRANCHNO = A.SALEBRANCHNO) SALEBRANCHNAME, "
				+ "A.CARDFACEID CARDFACEID, (SELECT C.CARDFACENAME FROM CARDFACE C WHERE C.CARDFACEID = A.CARDFACEID) CARDFACENAME, "
				+ "A.BALANCE BALANCE "
				+ "FROM ACTIVATECARD A "
				+ "WHERE A.BALANCE > 0 " + "AND A.BALANCE <= ?1 ";
		if (isSingle.equals("1")) {
			sql = sql
					+ "AND (A.SALEBRANCHNO IN (SELECT S.SUBBRANCHNO FROM SYSBRANCHSUBORD S WHERE S.BRANCHNO = "
					+ saleBranchNo + ") OR A.SALEBRANCHNO = " + saleBranchNo
					+ ") ";
		} else {
			sql = sql + "AND A.SALEBRANCHNO = " + saleBranchNo + " ";
		}
		sql = sql
				+ ") X "
				+ "LEFT JOIN (SELECT F.TRANTIME TRANTIME, F.CARDNO CARDNO FROM MCHTRADDETAIL F UNION ALL SELECT E.TRANTIME TRANTIME, E.CARDNO CARDNO "
				+ "FROM MCHHISTRADDETAIL E) Y ON Y.CARDNO = X.CARDNO "
				+ "GROUP BY X.CARDNO, X.SALEBRANCHNO, X.SALEBRANCHNAME, X.CARDFACEID, X.CARDFACENAME, X.BALANCE "
				+ "ORDER BY MAX(Y.TRANTIME) ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, balanceUpperLimit);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getActivateCardsResidualStatistics(String isSingle,
			String saleBranchNo, BigInteger balanceUpperLimit) {
		String sql = "SELECT A.SALEBRANCHNO SALEBRANCHNO, "
				+ "(SELECT B.SALEBRANCHNAME FROM SALEBRANCH B WHERE B.SALEBRANCHNO = A.SALEBRANCHNO) SALEBRANCHNAME, "
				+ "A.CARDCLASSICID CARDCLASSICID, "
				+ "(SELECT C.CARDCLASSICNAME FROM CARDCLASSIC C WHERE C.CARDCLASSICID = A.CARDCLASSICID) CARDCLASSICNAME,  "
				+ "COUNT(A.CARDNO), SUM(A.BALANCE) " + "FROM ACTIVATECARD A "
				+ "WHERE A.BALANCE > 0 AND A.BALANCE <= ?1 ";
		if (isSingle.equals("1")) {
			sql = sql
					+ "AND (A.SALEBRANCHNO IN (SELECT S.SUBBRANCHNO FROM SYSBRANCHSUBORD S WHERE S.BRANCHNO = '"
					+ saleBranchNo + "') OR A.SALEBRANCHNO = '" + saleBranchNo
					+ "') ";
		} else {
			sql = sql + "AND A.SALEBRANCHNO = '" + saleBranchNo + "' ";
		}
		sql = sql + "GROUP BY  A.SALEBRANCHNO, A.CARDCLASSICID "
				+ "ORDER BY A.SALEBRANCHNO, A.CARDCLASSICID";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, balanceUpperLimit);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardAccountManagementFeeDetail(String isSingle,
			String saleBranchNo, Date startDate, Date endDate) {
		String sql = "SELECT A.SALEBRANCHNO SALEBRANCHNO, " +
				"(SELECT X.SALEBRANCHNAME FROM SALEBRANCH X WHERE X.SALEBRANCHNO = A.SALEBRANCHNO) SALEBRANCHNAME, " +
				"A.CARDCLASSICID CARDCLASSICID, " +
				"(SELECT Y.CARDCLASSICNAME FROM CARDCLASSIC Y WHERE Y.CARDCLASSICID = A.CARDCLASSICID) CARDCLASSICNAME, " +
				"A.CARDNO CARDNO, DECODE(C.FEETYPE, 0, '固定金额', 1, '余额比率') FEETYPE, C.FEERATIO FEERATIO, " +
				"SUM(NVL(B.FEE,0)) FEE " +
				"FROM ACTIVATECARD A " +
				"JOIN MANAGEMENTFEEDETAIL B ON A.CARDNO = B.CARDNO " +
				"LEFT JOIN MANAGEMENTFEEPOLICY C ON A.MANAGEMENTFEEPOLICYID = C.FEEPOLICYID " +
				"WHERE B.TRANDATE <= ?2 AND B.TRANDATE >= ?1 ";;
		if (isSingle.equals("1")) {
			sql = sql
					+ "AND (A.SALEBRANCHNO IN (SELECT S.SUBBRANCHNO FROM SYSBRANCHSUBORD S WHERE S.BRANCHNO = '"
					+ saleBranchNo + "') OR A.SALEBRANCHNO = '" + saleBranchNo
					+ "') ";
		} else {
			sql = sql + "AND A.SALEBRANCHNO = '" + saleBranchNo + "' ";
		}
		sql = sql + "GROUP BY A.SALEBRANCHNO, A.CARDCLASSICID, A.CARDNO, C.FEETYPE, C.FEERATIO " +
				"ORDER BY A.SALEBRANCHNO, A.CARDCLASSICID, A.CARDNO, C.FEETYPE, C.FEERATIO";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startDate);
		query.setParameter(2, endDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardAccountManagementFeeStatistics(
			String isSingle, String saleBranchNo, Date startDate, Date endDate) {
		String sql = "SELECT S.SALEBRANCHNO, S.SALEBRANCHNAME, S.CARDCLASSICID, S.CARDCLASSICNAME, S.FEETYPE, S.FEERATIO, SUM(S.FEE) FROM "
				+ "(SELECT X.SALEBRANCHNO SALEBRANCHNO, "
				+ "(SELECT A.SALEBRANCHNAME FROM SALEBRANCH A WHERE A.SALEBRANCHNO = X.SALEBRANCHNO) SALEBRANCHNAME, "
				+ "X.CARDCLASSICID CARDCLASSICID, "
				+ "(SELECT B.CARDCLASSICNAME FROM CARDCLASSIC B WHERE B.CARDCLASSICID = X.CARDCLASSICID) CARDCLASSICNAME, "
				+ "X.CARDNO CARDNO, DECODE(Y.FEETYPE, 0, '固定金额', 1, '余额比率') FEETYPE, "
				+ "Y.FEERATIO FEERATIO, DECODE((SELECT SUM(C.FEE) FROM MANAGEMENTFEEDETAIL C WHERE C.CARDNO = X.CARDNO AND C.TRANDATE >= ?1 AND C.TRANDATE <= ?2), NULL, 0) FEE "
				+ "FROM ACTIVATECARD X "
				+ "LEFT JOIN MANAGEMENTFEEPOLICY Y ON  X.MANAGEMENTFEEPOLICYID = Y.FEEPOLICYID "
				+ "WHERE 1 = 1 ";
		if (isSingle.equals("1")) {
			sql = sql
					+ "AND (X.SALEBRANCHNO IN (SELECT S.SUBBRANCHNO FROM SYSBRANCHSUBORD S WHERE S.BRANCHNO = '"
					+ saleBranchNo + "') OR X.SALEBRANCHNO = '" + saleBranchNo
					+ "') ";
		} else {
			sql = sql + "AND X.SALEBRANCHNO = '" + saleBranchNo + "' ";
		}
		sql = sql
				+ ") S GROUP BY S.SALEBRANCHNO, S.SALEBRANCHNAME, S.CARDCLASSICID, S.CARDCLASSICNAME, S.FEETYPE, S.FEERATIO "
				+ "ORDER BY S.SALEBRANCHNO, S.SALEBRANCHNAME, S.CARDCLASSICID, S.CARDCLASSICNAME, S.FEETYPE, S.FEERATIO";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startDate);
		query.setParameter(2, endDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@Override
	public int getActivateCardTotalCountByBatchNo(String batchNo) {
		String sql = "select nvl(count(*),0) from activatecard t where t.batchno like ?1";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, "%" + batchNo);
		return Integer.parseInt(query.getSingleResult().toString());
	}
}
